package dl.transactionscript.gateway;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ResourceBundle;

import dl.database.connector.DbConnector;
import dl.exception.handler.ApplicationException;
import dl.martinfowler.wrapper.MfDate;
import dl.martinfowler.wrapper.Money;

public class TableDataGateway {

	private Connection connection = null;

	public TableDataGateway() {
		connection = DbConnector.getInstance().connect();
	}

	private static final String findRecognitionsStatement = "SELECT amount "
			+ "FROM revenueRecognitions "
			+ "WHERE contract = ? AND recognizedOn <= ?";

	private static final String findRevenueRecognitionsStatement = "SELECT * "
			+ "FROM revenueRecognitions ";

	private static final String findContractStatement = "SELECT * "
			+ "FROM contracts c, products p "
			+ "WHERE c.id = ? AND c.product = p.id";

	private static final String findProductStatement = "SELECT * "
			+ "FROM products p ";

	private static final String findProductContractStatement = "SELECT * "
			+ "FROM products p, contracts c " + "WHERE p.id = c.product";

	private static final String insertRecognitionStatement = "INSERT INTO revenuerecognitions "
			+ "VALUES (?, ?, ?)";

	private static final String insertContractStatement = "INSERT INTO contracts "
			+ "(product, revenue, datesigned) " + "VALUES (?, ?, ?)";

	private static final String insertProductStatement = "INSERT INTO products "
			+ "(name, type) " + "VALUES (?, ?)";

	private static final String deleteProductStatement = "DELETE FROM products "
			+ "WHERE id = ?";

	private static final String deleteContractStatement = "DELETE FROM contracts "
			+ "WHERE id = ?";

	public ResultSet findRecognitionsFor(long contractID, MfDate asOf)
			throws SQLException {
		PreparedStatement pstmt = connection
				.prepareStatement(findRecognitionsStatement);
		pstmt.setLong(1, contractID);
		pstmt.setDate(2, asOf.toSqlDate());
		ResultSet result = pstmt.executeQuery();
		return result;
	}

	public ResultSet findContract(long contractID) throws SQLException {
		PreparedStatement pstmt = connection
				.prepareStatement(findContractStatement);
		pstmt.setLong(1, contractID);
		ResultSet result = pstmt.executeQuery();
		return result;
	}

	public ResultSet findProductAll() throws SQLException {
		PreparedStatement pstmt = connection
				.prepareStatement(findProductStatement);
		ResultSet result = pstmt.executeQuery();
		return result;
	}

	public ResultSet findRevenueRecognitions() throws SQLException {
		PreparedStatement pstmt = connection
				.prepareStatement(findRevenueRecognitionsStatement);
		ResultSet result = pstmt.executeQuery();
		return result;
	}

	public ResultSet findProductContractAll() throws SQLException {
		PreparedStatement pstmt = connection
				.prepareStatement(findProductContractStatement);
		ResultSet result = pstmt.executeQuery();
		return result;
	}

	public void insertRecognition(long contractID, Money amount, MfDate asOf)
			throws SQLException {
		PreparedStatement pstmt = connection
				.prepareStatement(insertRecognitionStatement);
		pstmt.setLong(1, contractID);
		pstmt.setBigDecimal(2, amount.amount());
		pstmt.setDate(3, asOf.toSqlDate());
		pstmt.executeUpdate();
	}

	public long insertProduct(String name, String type) throws SQLException {
		PreparedStatement pstmt = connection.prepareStatement(
				insertProductStatement, Statement.RETURN_GENERATED_KEYS);
		pstmt.setString(1, name);
		pstmt.setString(2, type);
		pstmt.executeUpdate();
		ResultSet rs = pstmt.getGeneratedKeys();
		rs.next();
		return rs.getLong(1);
	}

	public long insertContract(long productID, Money revenue, MfDate dateSigned)
			throws SQLException {
		PreparedStatement pstmt = connection.prepareStatement(
				insertContractStatement, Statement.RETURN_GENERATED_KEYS);
		pstmt.setLong(1, productID);
		pstmt.setBigDecimal(2, revenue.amount());
		pstmt.setDate(3, dateSigned.toSqlDate());
		pstmt.executeUpdate();
		ResultSet rs = pstmt.getGeneratedKeys();
		if (rs.next())
			return rs.getLong(1);
		return 0;

	}

	public long insertProductWithContract(String productName,
			String productType, Money revenue, MfDate dateSigned)
			throws SQLException {
		long newProductID = insertProduct(productName, productType);
		if (newProductID != 0) {
			return insertContract(newProductID, revenue, dateSigned);
		}
		return 0;
	}

	public void deleteProduct(long productID) throws SQLException {
		PreparedStatement pstmt = connection
				.prepareStatement(deleteProductStatement);
		pstmt.setLong(1, productID);
		pstmt.executeUpdate();
	}

	public void deleteContract(long contractID) throws SQLException {
		PreparedStatement pstmt = connection
				.prepareStatement(deleteContractStatement);
		pstmt.setLong(1, contractID);
		pstmt.executeUpdate();
	}

	public Connection getConnection() {
		return this.connection;
	}

	public static void main(String[] args) {
		TableDataGateway gateway = new TableDataGateway();
		String queryString = "select * from products";
		try {
			PreparedStatement ps = gateway.getConnection().prepareStatement(
					queryString);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				System.out.println(rs.getInt(1));
				System.out.println(rs.getString(2));
				System.out.println(rs.getString(3));
			}
		} catch (Exception e) {
			e.printStackTrace();
		}

		if (gateway.getConnection() != null) {
			System.out.println("You made it, take control your database now!");
		} else {
			System.out.println("Failed to make connection!");
		}
	}

}
